package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.repository.EmergencyShelterRepository;
import com.css.fxfzypg.modules.baseData.entity.EmergencyShelter;
import com.css.fxfzypg.modules.baseData.queryParams.EmergencyShelterQueryParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class EmergencyShelterRepositoryImpl implements EmergencyShelterRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//    @Override
//    public ElevationVO updateElevation(ElevationVO elevationVO) {
//
//        String sql = "update sys_elevation set elevation_number = :elevationNumber, elevation_band = :elevationBand, elevation_nodatavalue = :elevationNodatavalue, elevation_min = :elevationMin, elevation_max = :elevationMax  where elevation_id = '"+elevationVO.getElevationId()+"'" ;
//
//        SqlParameterSource source = new BeanPropertySqlParameterSource(elevationVO);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        namedParameterJdbcTemplate.update(sql,source,keyHolder);
//        return elevationVO;
//    }

    @Override
    public EmergencyShelter saveEmergencyShelter(EmergencyShelter emergencyShelter) {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "";
                sql = "insert into sys_emergencyshelter (es_longitude,es_latitude,es_name,es_type,es_address,es_maxpeople,es_emergencysupportequipment,es_status,es_createtime) values(?,?,?,?,?,?,?,?,?) ";
                PreparedStatement ps = connection.prepareStatement(sql, new String [] {"es_id"});
                ps.setFloat(1, emergencyShelter.getEsLongitude());
                ps.setFloat(2, emergencyShelter.getEsLatitude());
                ps.setString(3, emergencyShelter.getEsName());
                ps.setString(4, emergencyShelter.getEsType());
                ps.setString(5, emergencyShelter.getEsAddress());
                ps.setInt(6, emergencyShelter.getEsMaxpeople());
                ps.setString(7, emergencyShelter.getEsEmergencysupportequipment());
                ps.setString(8, emergencyShelter.getEsStatus());
                java.sql.Date createDate = new java.sql.Date(emergencyShelter.getEsCreatetime().getTime());
                ps.setDate(9,  createDate);
                return ps;
            }
        }, keyHolder);

        int i = keyHolder.getKey().intValue();
        emergencyShelter.setEsId(i);
        return emergencyShelter;
    }

    @Override
    public Page<EmergencyShelter> getAllEmergencyShelterParams(EmergencyShelterQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  e.* from sys_emergencyshelter e ");

        StringBuilder orderSql = new StringBuilder(" order by e.es_id");

        StringBuilder whereSql = new StringBuilder(" where e.es_status = '" + YNEnum.Y.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getEsName()) ){
            whereSql.append(" and e.es_name like '%" + queryParams.getEsName() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getEsAddress()) ){
           whereSql.append(" and e.es_address like '%" + queryParams.getEsAddress() + "%'");
        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<EmergencyShelter> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(EmergencyShelter.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<EmergencyShelter> emergencyShelters = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return emergencyShelters;
    }

    @Override
    public EmergencyShelter getEmergencyShelterById(Integer id) {
        String sql = "select * from sys_emergencyshelter where es_id = '"+id+"'";
        EmergencyShelter emergencyShelter = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EmergencyShelter.class));
        return emergencyShelter;
    }

    @Override
    public void deleteEmergencySheltersById(List<Integer> ids, Timestamp timestamp) {
        String sql = "update sys_emergencyshelter set es_status = '" + YNEnum.N + "', es_updatetime='"+timestamp+"' " + " where es_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<EmergencyShelter> getAll() {
        return jdbcTemplate.query("select * from sys_emergencyshelter",new BeanPropertyRowMapper<>(EmergencyShelter.class));
    }

    @Override
    public void importExcel(List<EmergencyShelter> emergencyShelters) {
        jdbcTemplate.batchUpdate("insert into sys_emergencyshelter (es_longitude,es_latitude,es_name,es_type,es_address,es_maxpeople,es_emergencysupportequipment,es_status,es_createtime)values(?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return emergencyShelters.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setFloat(1,emergencyShelters.get(i).getEsLongitude());
                ps.setFloat(2,emergencyShelters.get(i).getEsLatitude());
                ps.setString(3,emergencyShelters.get(i).getEsName());
                ps.setString(4,emergencyShelters.get(i).getEsType());
                ps.setString(5,emergencyShelters.get(i).getEsAddress());
                ps.setInt(6,emergencyShelters.get(i).getEsMaxpeople());
                ps.setString(7,emergencyShelters.get(i).getEsEmergencysupportequipment());
                ps.setString(8,emergencyShelters.get(i).getEsStatus());
                java.sql.Date etimeDate = new java.sql.Date(emergencyShelters.get(i).getEsCreatetime().getTime()) ;
                ps.setDate(9,etimeDate);
            }
        });
    }

    @Override
    public List<EmergencyShelter> getEmergencyShelter(String influenceflyId) {
        String sql="SELECT * FROM sys_emergencyshelter t WHERE  ST_Intersects(t.geom,(select a.geom FROM seismic_influencefly_pbe a WHERE a.influencefly_id = ?)) and es_status='1'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(EmergencyShelter.class),influenceflyId);
    }
    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_emergencyshelter where county = '" + county + "' and city = '" + city + "' and province = '" + province + "'  or (province ='"+province+"' and city ='' and county ='') and es_status ='1' ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_emergencyshelter where city = '" + city + "' and province = '" + province + "' and es_status ='1' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_emergencyshelter where province = '" + province + "' and es_status ='1'", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_emergencyshelter where province = ? and (TO_CHAR(es_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(es_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and es_status ='1'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
